with dwd_tab_send_base_dt as(
    select
         send.waybill_no                                    as waybill_no         --运单号
        ,nvl(send.scan_time        ,load.scan_time        ) as scan_time          --扫描时间
        ,nvl(send.input_time       ,load.input_time       ) as input_time         --录入时间
        ,nvl(send.scan_site_code   ,load.scan_site_code   ) as scan_site_code     --扫描网点编码
        ,nvl(send.scan_site_name   ,load.scan_site_name   ) as scan_site_name     --扫描网点名称
        ,nvl(send.next_station_code,load.next_station_code) as next_station_code  --转单号
        ,nvl(send.transfer_code    ,load.transfer_code    ) as transfer_code      --转单号
        ,send.scan_type                                     as scan_type          --扫描类型
    from(
        select
             waybill_no            as waybill_no         --运单号
            ,scan_time             as scan_time          --扫描时间
            ,input_time            as input_time         --录入时间
            ,scan_site_code        as scan_site_code     --扫描网点编码
            ,input_site            as scan_site_name     --扫描网点名称
            ,next_station_code     as next_station_code  --转单号
            ,shipment_no           as transfer_code      --转单号
            ,'send'                as scan_type          --扫描类型
       from jms_dwd.dwd_tab_barscan_send_base_hi  --发件
       where substr(dt,1,10) between '{{ execution_date | cst_ds }}' and date_add('{{ execution_date | cst_ds }}',1)
        and scan_time between concat('{{ execution_date | cst_ds }}',' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 06:00:00')
        and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) send
    left join (
        select
             waybill_no            as waybill_no         --运单号
            ,scan_time             as scan_time          --扫描时间
            ,input_time            as input_time         --录入时间
            ,scan_site_code        as scan_site_code     --扫描网点编码
            ,scan_site             as scan_site_name     --扫描网点名称
            ,next_station_code     as next_station_code  --转单号
            ,shipment_no           as transfer_code      --转单号
            ,'load'                as scan_type
        from jms_dwd.dwd_tab_barscan_loading_base_hi --装车
        where substr(dt,1,10) between '{{ execution_date | cst_ds }}' and date_add('{{ execution_date | cst_ds }}',1)
         and scan_time between concat('{{ execution_date | cst_ds }}',' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 06:00:00')
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) load on send.waybill_no = load.waybill_no
          and send.scan_site_code = load.scan_site_code
),


dwd_tab_arrival_base_dt as (
    select
         arrival.waybill_no                                      as waybill_no         --运单号
        ,nvl(arrival.scan_time        ,unload.scan_time        ) as scan_time          --扫描时间
        ,nvl(arrival.input_time       ,unload.input_time       ) as input_time         --录入时间
        ,nvl(arrival.scan_site_code   ,unload.scan_site_code   ) as scan_site_code     --扫描网点编码
        ,nvl(arrival.scan_site_name   ,unload.scan_site_name   ) as scan_site_name     --扫描网点名称
        ,nvl(arrival.next_station_code,unload.next_station_code) as next_station_code  --转单号
        ,nvl(arrival.transfer_code    ,unload.transfer_code    ) as transfer_code      --转单号
        ,arrival.scan_type                                       as scan_type          --扫描类型
    from(
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,pre_station_code     as next_station_code  --转单号
            ,transfer_code        as transfer_code      --转单号
            ,'arrival'            as scan_type          --扫描类型
        from jms_dwd.dwd_tab_barscan_arrival_base_hi arrival --到件
        where substr(dt,1,10) between '{{ execution_date | cst_ds }}' and date_add('{{ execution_date | cst_ds }}',1)
         and scan_time between concat('{{ execution_date | cst_ds }}',' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 06:00:00')
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) arrival
    left join (
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,pre_station_code     as next_station_code  --转单号
            ,shipment_no          as transfer_code      --转单号
            ,'unload'             as scan_type          --扫描类型
        from jms_dwd.dwd_tab_barscan_unloading_base_hi --卸车
        where substr(dt,1,10) between '{{ execution_date | cst_ds }}' and date_add('{{ execution_date | cst_ds }}',1)
         and scan_time between concat('{{ execution_date | cst_ds }}',' 00:00:00') and concat(date_add('{{ execution_date | cst_ds }}',1),' 06:00:00')
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) unload on arrival.waybill_no = unload.waybill_no
            and arrival.scan_site_code = unload.scan_site_code
            and arrival.scan_time = unload.scan_time
),



dwd_tab_arrival_unload_base_dt as (
    select *
    from(
        select
             scan.waybill_no            as waybill_no          --运单号
            ,scan.scan_time             as scan_time           --扫描时间
            ,scan.input_time            as input_time          --录入时间
            ,dim.code                   as scan_site_code      --扫描网点编码
            ,dim.name                   as scan_site_name      --扫描网点名称
            ,scan.next_station_code     as next_station_code   --上下一站
            ,scan.transfer_code         as transfer_code       --转单号
            ,scan.scan_type             as scan_type           --扫描类型
            ,dim.network_type           as network_type
            ,next_dim.network_type      as next_network_type
            ,row_number() over(partition by scan.waybill_no,dim.network_type order by scan_time) as rn --按照每个运单站点的最早时间排序
        from (
            select
                 waybill_no         --运单号
                ,scan_time          --扫描时间
                ,input_time         --录入时间
                ,scan_site_code     --扫描网点编码
                ,scan_site_name     --扫描网点名称
                ,next_station_code  --转单号
                ,transfer_code      --转单号
                ,scan_type          --扫描类型
            from dwd_tab_arrival_base_dt
        ) scan
        left join (
            select
                  code
                 ,name
                 ,network_type
            from jms_dim.dim_network_whole_massage --网点维度表 当前站
        ) dim on scan.scan_site_code = dim.code
        left join (
            select
                 code
                ,name
                ,network_type
            from jms_dim.dim_network_whole_massage --网点维度表 下一站
        ) next_dim on scan.next_station_code = next_dim.code
    ) a where a.rn = 1
    union all
    select *
    from(
        select
             scan.waybill_no            as waybill_no          --运单号
            ,scan.scan_time             as scan_time           --扫描时间
            ,scan.input_time            as input_time          --录入时间
            ,dim.code                   as scan_site_code      --扫描网点编码
            ,dim.name                   as scan_site_name      --扫描网点名称
            ,scan.next_station_code     as next_station_code   --上下一站
            ,scan.transfer_code         as transfer_code       --转单号
            ,scan.scan_type             as scan_type           --扫描类型
            ,dim.network_type           as network_type
            ,next_dim.network_type      as next_network_type
            ,row_number() over(partition by scan.waybill_no,dim.network_type order by scan_time asc) as rn --按照每个运单站点类型排序取出始发中心最晚装车发件站点
        from (
            select
                 waybill_no         --运单号
                ,scan_time          --扫描时间
                ,input_time         --录入时间
                ,scan_site_code     --扫描网点编码
                ,scan_site_name     --扫描网点名称
                ,next_station_code  --转单号
                ,transfer_code      --转单号
                ,scan_type          --扫描类型
                ,row_number() over(partition by waybill_no,scan_site_code order by scan_time desc) as rk --按照每个运单站点的最晚时间排序
            from dwd_tab_send_base_dt
        ) scan
        left join (
            select
                  code
                 ,name
                 ,network_type
            from jms_dim.dim_network_whole_massage --网点维度表 当前站
        ) dim on scan.scan_site_code = dim.code
        left join (
            select
                 code
                ,name
                ,network_type
            from jms_dim.dim_network_whole_massage --网点维度表 下一站
        ) next_dim on scan.next_station_code = next_dim.code
        where scan.rk = 1
    ) a where a.rn = 1
),


dwd_tab_send_next_base_dt as (
   select *
   from (
       select
             *,row_number() over(partition by waybill_no,next_station_code order by scan_time desc) as rk --按照最晚取，
       from dwd_tab_send_base_dt --装车发件
       where next_station_code is not null
   ) a where a.rk = 1
)



---------------------------第一段：拿出T-1天到T天7点到发件数据取出始发网点、始发集散、始发中心
insert overwrite table jms_tmp.dwd_tab_barscan_base_dt_T1
select *
from (
    select
         arrival.waybill_no            as waybill_no         --运单号
        ,arrival.scan_time             as scan_time          --扫描时间
        ,arrival.input_time            as input_time         --录入时间
        ,arrival.scan_site_code        as scan_site_code     --扫描网点编码
        ,arrival.scan_site_name        as scan_site_name     --扫描网点名称
        ,send.scan_site_code           as next_station_code  --转单号
        ,arrival.transfer_code         as transfer_code      --转单号
        ,arrival.network_type          as network_type
        ,arrival.next_network_type     as next_network_type
        ,arrival.scan_type             as scan_type          --扫描类型
    from dwd_tab_arrival_unload_base_dt arrival  --卸车到件
    left join dwd_tab_send_next_base_dt send
        on arrival.waybill_no = send.waybill_no    --关联发件取发件当前站 = 到件上一站
       and arrival.scan_site_code = send.next_station_code
       and arrival.scan_time > send.scan_time
    where arrival.scan_type = 'arrival' --到件
    union all
    select
          waybill_no        --运单号
         ,scan_time         --扫描时间
         ,input_time        --录入时间
         ,scan_site_code    --扫描网点编码
         ,scan_site_name    --扫描网点名称
         ,next_station_code --转单号
         ,transfer_code     --转单号
         ,network_type
         ,next_network_type
         ,scan_type         --扫描类型
    from dwd_tab_arrival_unload_base_dt  --发件
    where scan_type = 'send'
) scan
;



insert overwrite table jms_tmp.dwd_tab_barscan_first_center_base_dt
select
     base.waybill_no                 as waybill_no                   --运单号
    ,base.first_center_send_time     as first_center_send_time       --始发转运中心扫描时间
    ,base.first_center_arrival_time  as first_center_arrival_time    --始发转运中心扫描时间
    ,least(nvl(branch.arrival_center_arrival_time,'2099-12-31 00:00:00')
          ,nvl(branch.send_center_arrival_time   ,'2099-12-31 00:00:00')
          ,nvl(base.first_center_arrival_time    ,'2099-12-31 00:00:00')
          ,nvl(base.first_center_send_time       ,'2099-12-31 00:00:00')
     ) as first_center_actual_time      --实际首中心到车时间 兜底逻辑到件、发件下一站、卸车、装车下一站,取最早
    ,coalesce(branch.arrival_center_shipment_no ,branch.send_center_shipment_no ) as first_center_shipment_no   --实际到车任务号
    ,case when base.first_center_arrival_time < nvl(base.first_center_send_time,'2099-99-99') then base.first_center_arrival_code
          else base.first_center_send_code end as first_center_code  --始发转运中心发件编码
    ,case when base.first_center_arrival_time < nvl(base.first_center_send_time,'2099-99-99') then base.first_center_arrival_name
          else base.first_center_send_name end as first_center_name  --始发转运中心发件名称
from (
    select
         waybill_no
        ,max(first_center_send_time   ) as first_center_send_time
        ,max(first_center_send_code   ) as first_center_send_code
        ,max(first_center_send_name   ) as first_center_send_name
        ,max(first_center_arrival_time) as first_center_arrival_time
        ,max(first_center_arrival_code) as first_center_arrival_code
        ,max(first_center_arrival_name) as first_center_arrival_name
    from (
        select
              waybill_no
             ,case when network_type = 4 and scan_type = 'send'    then scan_time      end as first_center_send_time        --始发转运中心扫描时间
             ,case when network_type = 4 and scan_type = 'send'    then scan_site_code end as first_center_send_code        --始发转运中心编码
             ,case when network_type = 4 and scan_type = 'send'    then scan_site_name end as first_center_send_name        --始发转运中心名称
             ,case when network_type = 4 and scan_type = 'arrival' then scan_time      end as first_center_arrival_time     --始发转运中心扫描时间
             ,case when network_type = 4 and scan_type = 'arrival' then scan_site_code end as first_center_arrival_code     --始发转运中心编码
             ,case when network_type = 4 and scan_type = 'arrival' then scan_site_name end as first_center_arrival_name     --始发转运中心名称
        from jms_tmp.dwd_tab_barscan_base_dt_T1
    ) a group by waybill_no
) base
left join (
    select
         waybill_no                       as waybill_no              --运单号
        ,max(arrival_center_arrival_time) as arrival_center_arrival_time --优先取到件扫描对应支线实际到达时间
        ,max(send_center_arrival_time   ) as send_center_arrival_time    --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_shipment_no ) as arrival_center_shipment_no  --优先取到件扫描对应支线任务号
        ,max(send_center_shipment_no    ) as send_center_shipment_no     --次取发件扫描对应支线任务号
    from (
         select
               base.waybill_no     --运单号
              ,base.scan_time      --扫描时间
              ,base.input_time     --录入时间
              ,base.scan_site_code --扫描网点编码
              ,base.scan_site_name --扫描网点名称
              ,base.scan_type      --扫描类型
              ,base.next_station_code
              ,case when base.network_type  = 4 and base.scan_type = 'arrival' then stop.actual_arrival_time end as arrival_center_arrival_time --优先取到件扫描对应支线实际到达时间
              ,case when base.network_type  = 4 and base.scan_type = 'arrival' then stop.shipment_no         end as arrival_center_shipment_no  --优先取到件扫描对应支线任务号
              ,case when base.network_type <> 4 and base.next_network_type = 4 and base.scan_type = 'send' then stop.actual_arrival_time end as send_center_arrival_time --次取发件扫描对应支线实际到达时间
              ,case when base.network_type <> 4 and base.next_network_type = 4 and base.scan_type = 'send' then stop.shipment_no         end as send_center_shipment_no  --次取发件扫描对应支线任务号
        from (
             select *
             from (
                  select
                       *,row_number() over(partition by waybill_no,scan_type,case when scan_type = 'send' then next_network_type else network_type end order by scan_time) as rn
                  from jms_tmp.dwd_tab_barscan_base_dt_T1 base
             ) a where a.rn = 1
        ) base
        left join (
            select
                 a.shipment_no         as shipment_no
                ,a.sort_num            as sort_num
                ,a.network_code        as network_code        --经停网点编码
                ,a.network_name        as network_name        --经停网点名称
                ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                ,b.network_code        as pre_network_code    --经停网点编码
                ,b.network_name        as pre_network_name    --经停网点名称
            from (
                select *
                from (
                    select
                         shipment_no --任务号
                        ,sort_num
                        ,network_code --经停网点编码
                        ,network_name --经停网点名称
                        ,actual_arrival_time --最晚到车时间
                        ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                    from jms_ods.yl_tmsnew_branch_shipment_stop_hi  --支线经停表
                    where substr(dt,1,10)  between date_sub('{{ execution_date | cst_ds }}',7) and date_add('{{ execution_date | cst_ds }}',1)
                      and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',7) and date_add('{{ execution_date | cst_ds }}',1)
                ) a where a.rn = 1
            ) a
            left join (
                select *
                from (
                    select
                         shipment_no --任务号
                        ,sort_num
                        ,network_code --经停网点编码
                        ,network_name --经停网点名称
                        ,actual_arrival_time --最晚到车时间
                        ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                    from jms_ods.yl_tmsnew_branch_shipment_stop_hi  --支线经停表
                    where substr(dt,1,10)  between date_sub('{{ execution_date | cst_ds }}',7) and date_add('{{ execution_date | cst_ds }}',1)
                      and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',7) and date_add('{{ execution_date | cst_ds }}',1)
                ) a where a.rn = 1
            ) b on a.shipment_no = b.shipment_no
             and a.sort_num -1 = b.sort_num
        ) stop on stop.shipment_no = nvl(base.transfer_code,base.waybill_no) --优先取到件任务号的最晚到车时间
              and stop.network_code = case when base.scan_type in ('send') then base.next_station_code
                                          when base.scan_type in ('arrival') and stop.pre_network_code = base.next_station_code then base.scan_site_code end  --兜底逻辑到件、发件下一站、卸车、装车下一站
    ) a group by waybill_no
) branch on base.waybill_no = branch.waybill_no;





insert overwrite table jms_dm.dm_outport_effect_detail_dt
select
     waybill_no                   as waybill_no                  --运单号
    ,scan_date                    as scan_date                   --揽收/入仓日期
    ,sender_network_code          as sender_network_code         --寄件网点编码
    ,sender_network_name          as sender_network_name         --寄件网点名称
    ,sender_fran_code             as sender_fran_code            --寄件加盟商编码
    ,sender_fran_name             as sender_fran_name            --寄件加盟商名称
    ,sender_agent_code            as sender_agent_code           --寄件代理区编码
    ,sender_agent_name            as sender_agent_name           --寄件代理区名称
    ,sender_area_code             as sender_area_code            --寄件区县编码
    ,sender_area_name             as sender_area_name            --寄件区县名称
    ,sender_city_code             as sender_city_code            --寄件城市编码
    ,sender_city_name             as sender_city_name            --寄件城市名称
    ,taking_time                  as taking_time                 --揽收时间
    ,warehouse_time               as warehouse_time              --入仓时间
    ,first_center_code            as first_center_code           --始发转运中心发件编码
    ,first_center_name            as first_center_name           --始发转运中心发件名称
    ,first_center_plan_time       as first_center_plan_time      --始发转运中心规划到达时间
    ,first_center_actual_time     as first_center_actual_time    --始发转运中心实际到达时间
    ,first_center_shipment_no     as first_center_shipment_no    --始发转运中心实际任务号
    ,first_center_arrival_time    as first_center_arrival_time   --始发转运中心到件扫描时间
    ,first_center_send_time       as first_center_send_time      --始发转运中心发件扫描时间
    ,last_center_code             as last_center_code            --目的转运中心编码
    ,last_center_name             as last_center_name            --目的转运中心名称
    ,last_network_code            as last_network_code           --目的网点编码
    ,last_network_name            as last_network_name           --目的网点名称
    ,last_fran_code               as last_fran_code              --目的加盟商编码
    ,last_fran_name               as last_fran_name              --目的加盟商名称
    ,last_agent_code              as last_agent_code             --目的代理区编码
    ,last_agent_name              as last_agent_name             --目的代理区名称
    ,last_area_code               as last_area_code              --目的区县编码
    ,last_area_name               as last_area_name              --目的区县名称
    ,last_city_code               as last_city_code              --目的城市编码
    ,last_city_name               as last_city_name              --目的城市名称
    ,if_zhund                     as if_zhund                    --准点件
    ,if_liuc                      as if_liuc                     --留仓件
    ,if_weid                      as if_weid                     --未到件
    ,if_wand                      as if_wand                     --晚到件
    ,customer_code                as customer_code               --客户编号
    ,customer_name                as customer_name               --客户名称
    ,ordersource_code             as ordersource_code            --订单来源编码
    ,ordersource_name             as ordersource_name            --订单来源名称
    ,taking_user_code             as taking_user_code            --揽收业务员编码
    ,taking_user_name             as taking_user_name            --揽收业务员名称
    ,dt                           as dt                          --分区时间
from(
    select
          wide.waybill_no                 as waybill_no                  --运单号
         ,wide.scan_date                  as scan_date                   --揽收日期
         ,dim.code                        as sender_network_code         --寄件网点编码
         ,dim.name                        as sender_network_name         --寄件网点名称
         ,dim.fran_code                   as sender_fran_code            --寄件加盟商编码
         ,dim.fran_name                   as sender_fran_name            --寄件加盟商名称
         ,dim.agent_code                  as sender_agent_code           --寄件代理区编码
         ,dim.agent_name                  as sender_agent_name           --寄件代理区名称
         ,dim.area_id                     as sender_area_code            --寄件区县编码
         ,dim.area_desc                   as sender_area_name            --寄件区县名称
         ,dim.city_id                     as sender_city_code            --寄件城市编码
         ,dim.city_desc                   as sender_city_name            --寄件城市名称
         ,wide.taking_time                as taking_time                 --揽收时间
         ,wide.warehouse_time             as warehouse_time              --入仓时间
         ,base.first_center_code          as first_center_code           --始发转运中心发件编码
         ,base.first_center_name          as first_center_name           --始发转运中心发件名称
         ,case when base.first_center_arrival_time is not null or base.first_center_send_time is not null then base.first_center_actual_time end as first_center_actual_time --始发转运中实际到达时间
         ,case when base.first_center_arrival_time is not null or base.first_center_send_time is not null then base.first_center_shipment_no end as first_center_shipment_no --始发转运中实际任务号
         ,base.first_center_arrival_time  as first_center_arrival_time   --始发转运中心到件扫描时间
         ,base.first_center_send_time     as first_center_send_time      --始发转运中心发件扫描时间
         ,first.last_center_code          as last_center_code            --目的转运中心编码
         ,first.last_center_name          as last_center_name            --目的转运中心名称
         ,dim_last.code                   as last_network_code           --目的网点编码
         ,dim_last.name                   as last_network_name           --目的网点名称
         ,dim_last.fran_code              as last_fran_code              --目的加盟商编码
         ,dim_last.fran_name              as last_fran_name              --目的加盟商名称
         ,dim_last.agent_code             as last_agent_code             --目的代理区编码
         ,dim_last.agent_name             as last_agent_name             --目的代理区名称
         ,dim_last.area_id                as last_area_code              --目的区县编码
         ,dim_last.area_desc              as last_area_name              --目的区县名称
         ,dim_last.city_id                as last_city_code              --目的城市编码
         ,dim_last.city_desc              as last_city_name              --目的城市名称
         ,concat(date_add('{{ execution_date | cst_ds }}',1),config.plan_time) as first_center_plan_time --始发中心规划到车时间
         ,case when base.first_center_actual_time <= concat(date_add('{{ execution_date | cst_ds }}',1),config.plan_time)
               and (base.first_center_arrival_time is not null or base.first_center_send_time is not null) then 1 else 0 end as if_zhund --准点件
         ,case when (base.first_center_arrival_time is null and base.first_center_send_time is null)
                 or if(base.first_center_arrival_time is not null or base.first_center_send_time is not null,base.first_center_actual_time,'0000-00-00 00:00:00')
                  > if(config.plan_time is not null,concat(date_add('{{ execution_date | cst_ds }}',1),config.plan_time),'1999-00-00 00:00:00') then 1 else 0 end as if_liuc --留仓件
         ,case when base.first_center_arrival_time is null and base.first_center_send_time is null then 1 else 0 end as if_weid  --未到件
         ,case when if(base.first_center_arrival_time is not null or base.first_center_send_time is not null,base.first_center_actual_time,'0000-00-00 00:00:00') >
                    if(config.plan_time is not null,concat(date_add('{{ execution_date | cst_ds }}',1),config.plan_time),'1999-00-00 00:00:00') then 1 else 0 end as if_wand  --晚到件
         ,wide.customer_code    as customer_code    --客户编号
         ,wide.customer_name    as customer_name    --客户名称
         ,wide.ordersource_code as ordersource_code --订单来源编码
         ,wide.ordersource_name as ordersource_name --订单来源名称
         ,wide.taking_user_code as taking_user_code --揽收业务员编码
         ,wide.taking_user_name as taking_user_name --揽收业务员名称
         ,wide.scan_date as dt
    from (
        select
             wide.*
            ,tak.taking_user_code
            ,tak.taking_user_name
        from (
            select
                  waybill_no                  as waybill_no
                 ,dispatch_network_code       as last_network_code   --目的网点编码
                 ,dispatch_network_name       as last_network_name   --目的网点名称
                 ,taking_early_scantime       as taking_time         --最早揽收时间
                 ,warehousing_early_scantime  as warehouse_time      --最早入仓时间
                 ,first_code                  as first_code          --一段码
                 ,nvl(taking_early_scansitecode,warehousing_early_scansitecode)    as early_scan_site_code --收件网点
                 ,least(taking_early_scantime,warehousing_early_scantime)          as scan_time --最早揽收时间
                 ,to_date(least(taking_early_scantime,warehousing_early_scantime)) as scan_date --最早揽收时间
                 ,customer_code           as customer_code      --客户编号
                 ,customer_name           as customer_name      --客户名称
                 ,ordersource_code        as ordersource_code   --订单来源编码
                 ,case when ordersource_name in ('紫金山','逍遥峰','桃花岛','七星潭','桃花岛跨境','桃花岛跨境（逆）','极地湾','紫金山官方代发')
                       then ordersource_name else '其他' end as ordersource_name --订单来源名称
            from jms_dwd.dwd_wide_unsign_summary_waybill_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
              and to_date(least(taking_early_scantime,warehousing_early_scantime)) = '{{ execution_date | cst_ds }}'
              and is_intercept <> 1 --剔除拦截件
              and is_end_piece <> 1 --剔除完结件
              and nvl(taking_early_scansitecode,warehousing_early_scansitecode) <> dispatch_network_code --自收自派
        ) wide
        left join (
            select *
            from(
                select
                     waybill_no
                    ,scan_site_code
                    ,deliver_user_code as taking_user_code
                    ,deliver_user      as taking_user_name
                    ,row_number() over(partition by waybill_no order by scan_time asc) as rn 
                from jms_dwd.dwd_tab_barscan_taking_base_dt
                where dt = '{{ execution_date | cst_ds }}'
            ) a where a.rn = 1 
        ) tak on wide.waybill_no = tak.waybill_no 
             and wide.early_scan_site_code = nvl(tak.scan_site_code,tak.waybill_no)
    ) wide
    left join (
        select
             waybill_no                 as waybill_no                   --运单号
            ,first_center_send_time     as first_center_send_time       --始发转运中心扫描时间
            ,first_center_arrival_time  as first_center_arrival_time    --始发转运中心扫描时间
            ,case when first_center_actual_time = '2099-12-31 00:00:00' then null else first_center_actual_time end as first_center_actual_time --实际首中心到车时间
            ,first_center_shipment_no   as first_center_shipment_no     --实际到车任务号
            ,first_center_code          as first_center_code            --始发转运中心发件编码
            ,first_center_name          as first_center_name            --始发转运中心发件名称
        from jms_tmp.dwd_tab_barscan_first_center_base_dt --到件发件合并数据
    ) base on wide.waybill_no = base.waybill_no
    left join (
        select
              code
             ,name
             ,city_id
             ,city_desc
             ,manage_code
             ,manage_name
             ,agent_code
             ,agent_name
             ,fran_code
             ,fran_name
             ,area_id
             ,area_desc
             ,network_type
        from jms_dim.dim_network_whole_massage --网点维度表
    ) dim on wide.early_scan_site_code = dim.code
    left join (
        select
              code
             ,name
             ,city_id
             ,city_desc
             ,manage_code
             ,manage_name
             ,agent_code
             ,agent_name
             ,fran_code
             ,fran_name
             ,area_id
             ,area_desc
             ,network_type
        from jms_dim.dim_network_whole_massage --网点维度表
    ) dim_last on wide.last_network_code = dim_last.code
    left join (
        select
              code        as code
             ,center_code as last_center_code  --目的转运中心编码
             ,center_name as last_center_name  --目的转运中心名称
        from jms_dim.dim_lmdm_sys_first_code
        where is_enable=1
        and is_delete = 1
        group by code,center_name,center_code
    ) first on wide.first_code = first.code
    left join (
        select
              center_code
             ,center_name
             ,agent_code
             ,agent_name
             ,case when agent_code in (110000)                                 then ' 01:00:00'  -- 110000    北京代理区                                     01:00
                   when agent_code in (120000)                                 then ' 01:00:00'  -- 120000    天津代理区                                     01:00
                   when agent_code in (130000)                                 then ' 01:00:00'  -- 130000    河北代理区                                     01:00
                   when agent_code in (330200) and center_code in ('457401')   then ' 03:00:00'  -- 330200    宁波代理区      457101      宁波转运中心       03:00
                   when agent_code in (330200) and center_code in ('457401A1') then ' 03:00:00'  -- 330200    宁波代理区      457101      宁波转运中心A1     03:00
                   when agent_code in (330200)                                 then ' 01:30:00'  -- 330200    宁波代理区                                     01:30
                   when agent_code in (330100) and center_code in ('457101')   then ' 02:00:00'  -- 330100    杭州代理区      457101      杭州转运中心       02:00
                   when agent_code in (330100) and center_code in ('457302')   then ' 02:00:00'  -- 330100    杭州代理区      457302      嘉兴极兔极嘉分拨   02:00
                   when agent_code in (330100)                                 then ' 01:30:00'  -- 330100    杭州代理区                                     01:30
                   when agent_code in (330001) and center_code in ('457701')   then ' 01:00:00'  -- 330001    温台代理区      457701      温州转运中心       01:00
                   when agent_code in (330001) and center_code in ('457601')   then ' 01:00:00'  -- 330001    温台代理区      457601      台州转运中心       01:00
                   when agent_code in (330001)                                 then ' 01:30:00'  -- 330001    温台代理区                                     01:30
                   when agent_code in (330700)                                 then ' 01:30:00'  -- 330700    金华代理区                                     01:30
                   when agent_code in (500000)                                 then ' 01:30:00'  -- 500000    重庆代理区                                     01:30
                   when agent_code in (330000)                                 then ' 01:30:00'  -- 330000    浙江代理区                                     01:30
                   when agent_code in (530000)                                 then ' 02:00:00'  -- 530000    云南代理区                                     02:00
                   when agent_code in (440001)                                 then ' 01:30:00'  -- 440001    粤东代理区                                     01:30
                   when agent_code in (650000)                                 then ' 01:30:00'  -- 650000    新疆代理区                                     01:30
                   when agent_code in (320002) and center_code in ('351301')   then ' 00:59:00'  -- 320002    苏南代理区      351301      南通转运中心       00:59
                   when agent_code in (320002) and center_code in ('351001')   then ' 23:40:00'  -- 320002    苏南代理区      351001      无锡转运中心       23:40
                   when agent_code in (320002) and center_code in ('351901')   then ' 23:30:00'  -- 320002    苏南代理区      351901      常州转运中心       23:30
                   when agent_code in (320001) and center_code in ('351701')   then ' 23:30:00'  -- 320001    苏北代理区      351701      淮安转运中心       23:30
                   when agent_code in (320001) and center_code in ('302501')   then ' 23:00:00'  -- 320001    苏北代理区      302501      南京转运中心       23:00
                   when agent_code in (320001) and center_code in ('351601')   then ' 21:30:00'  -- 320001    苏北代理区      351601      徐州转运中心       21:30
                   when agent_code in (320001) and center_code in ('352301')   then ' 22:00:00'  -- 320001    苏北代理区      352301      泰州转运中心       22:00
                   when agent_code in (310000)                                 then ' 02:00:00'  -- 310000    上海代理区                                     02:00
                   when agent_code in (610000)                                 then ' 23:00:00'  -- 610000    陕西代理区                                     23:00
                   when agent_code in (140000)                                 then ' 01:30:00'  -- 140000    山西代理区                                     01:30
                   when agent_code in (370000)                                 then ' 00:00:00'  -- 370000    山东代理区                                     00:00
                   when agent_code in (640000)                                 then ' 06:00:00'  -- 640000    宁夏代理区                                     06:00
                   when agent_code in (150000) and center_code in ('847101')   then ' 21:00:00'  -- 150000    内蒙古代理区    847101      呼市转运中心       21:00
                   when agent_code in (150000) and center_code in ('847201')   then ' 20:00:00'  -- 150000    内蒙古代理区    847201      包头转运中心       20:00
                   when agent_code in (210000)                                 then ' 01:30:00'  -- 210000    辽宁代理区                                     01:30
                   when agent_code in (360000)                                 then ' 01:30:00'  -- 360000    江西代理区                                     01:30
                   when agent_code in (220000)                                 then ' 01:30:00'  -- 220000    吉林代理区                                     01:30
                   when agent_code in (430000)                                 then ' 06:00:00'  -- 430000    湖南代理区                                     06:00
                   when agent_code in (420000)                                 then ' 01:30:00'  -- 420000    湖北代理区                                     01:30
                   when agent_code in (230000)                                 then ' 01:30:00'  -- 230000    黑龙江代理区                                   01:30
                   when agent_code in (410000)                                 then ' 02:00:00'  -- 410000    河南代理区                                     02:00
                   when agent_code in (460000)                                 then ' 04:00:00'  -- 460000    海南代理区                                     04:00
                   when agent_code in (520000)                                 then ' 06:00:00'  -- 520000    贵州代理区                                     06:00
                   when agent_code in (450000) and center_code in ('277101')   then ' 03:00:00'  -- 450000    广西代理区      277101      南宁转运中心       03:00
                   when agent_code in (450000) and center_code in ('277201')   then ' 01:00:00'  -- 450000    广西代理区      277201      柳州转运中心       01:00
                   when agent_code in (450000) and center_code in ('277301')   then ' 19:30:00'  -- 450000    广西代理区      277301      桂林转运中心       19:30
                   when agent_code in (440100) and center_code in ('102002')   then ' 06:00:00'  -- 440100    广州代理区      175501      广州里水转运中心   05:30
                   when agent_code in (440100) and center_code in ('102001')   then ' 02:00:00'  -- 440100    广州代理区      175501      广州花都转运中心   05:30
                   when agent_code in (440100)                                 then ' 01:30:00'  -- 440100    广州代理区                                     01:30
                   when agent_code in (440002) and center_code in ('176001')   then ' 04:00:00'  -- 440002    粤西代理区      176001      中山转运中心       04:30
                   when agent_code in (440002) and center_code in ('166801')   then ' 22:00:00'  -- 440002    粤西代理区      166801      茂名转运中心       22:00
                   when agent_code in (440002)                                 then ' 01:30:00'  -- 440002    粤西代理区                                     01:30
                   when agent_code in (441900) and center_code in ('176901')   then ' 05:00:00'  -- 441900    东莞代理区      175501      东莞虎门转运中心   04:00
                   when agent_code in (441900)                                 then ' 06:00:00'  -- 441900    东莞代理区                                     06:00
                   when agent_code in (440300) and center_code in ('175501')   then ' 05:00:00'  -- 440300    深圳代理区      175501      深圳转运中心       04:00
                   when agent_code in (440300)                                 then ' 06:00:00'  -- 440300    深圳代理区                                     06:00
                   when agent_code in (620000)                                 then ' 06:00:00'  -- 620000    甘肃代理区                                     06:00
                   when agent_code in (350000)                                 then ' 01:30:00'  -- 350000    福建代理区                                     01:30
                   when agent_code in (510000)                                 then ' 00:30:00'  -- 510000    川藏代理区                                     00:30
                   when agent_code in (340000)                                 then ' 03:00:00'  -- 340000    安徽代理区                                     03:00
                   when agent_code in (630000)                                 then ' 06:00:00'  -- 630000    青海代理区                                     06:00
                   when agent_code in (540000)                                 then ' 00:30:00'  -- 540000	  西藏代理区                                     00:30
                   end as plan_time
        from jms_dim.dim_network_whole_massage
        group by center_code
                ,center_name
                ,agent_code
                ,agent_name
    ) config on config.center_code = base.first_center_code
) base
distribute by dt,pmod(hash(rand()),30);
